﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data.Common;
using Newtonsoft.Json;
using System.Data;
using PDAMain.Entity;
using System.Xml;
using System.IO;

namespace PDAMain.BLL
{
    /// <summary>
    /// 處理對數據庫的操作，查詢，插入及更新
    /// </summary>
    public class PublicMethods
    {
        private static string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase.ToString());
        public static string ConfigPath = path + @"\Config.xml";
        private static string dbName = "Sample.db3";

        /// <summary>
        /// 執行對數據庫操作的SQL
        /// </summary>
        /// <param name="strSql">SQL語句</param>
        /// <returns></returns>
        public static DataSet SelectData(string strSql)
        {
            DataSet ds = new DataSet();
            string dbPath = "data source=\"" + path + "\\" + dbName + "\"";
            SQLiteConnection conn = new SQLiteConnection();
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            try
            {
                conn.ConnectionString = dbPath;
                if (conn.State.ToString() == "Closed") 
                    conn.Open();

                cmd.CommandText = strSql;
                cmd.Connection = conn;

                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);

                return ds;
            }
            catch(Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 執行對數據庫操作的SQL
        /// </summary>
        /// <param name="strSql">SQL語句</param>
        /// <returns></returns>
        public static bool JudageData(string strSql)
        {
            bool result = false;
            DataSet ds = new DataSet();
            string dbPath = "data source=\"" + path + "\\" + dbName + "\"";
            SQLiteConnection conn = new SQLiteConnection();
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            try
            {
                conn.ConnectionString = dbPath;
                if (conn.State.ToString() == "Closed")
                    conn.Open();

                cmd.CommandText = strSql;
                cmd.Connection = conn;
                SQLiteDataAdapter da = new SQLiteDataAdapter(strSql,conn);
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    result = false;
                }
                else
                {
                    result = true;
                }
                /*SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);*/
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        /// <summary>
        /// 執行對數據庫操作的SQL,
        /// 1、插入更新刪除數據
        /// 2、新建刪除數據表
        /// </summary>
        /// <param name="strSql">SQL語句</param>
        /// <returns></returns>
        public static bool InsertUpdateCreateDropData(string strSql)
        {
            int i = 0;
            bool Flag = false;
            string dbPath = "data source=\"" + path + "\\" + dbName + "\"";
            SQLiteConnection conn = new SQLiteConnection();
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            try
            {
                conn.ConnectionString = dbPath;
                if (conn.State.ToString() == "Closed")
                    conn.Open();
                 
                cmd.CommandText = strSql;
                cmd.Connection = conn;

                i = cmd.ExecuteNonQuery();

                if (i > 0)
                    Flag = true;
                return Flag;
            }
            catch(Exception ex)
            {
                return Flag;
            }
            finally
            {
                conn.Close();
            }
        }
        
        /// <summary>
        /// 验证数据类型
        /// </summary>
        /// <param name="strVerifyData">验证的数据</param>
        /// <param name="strVerifyType">数据类型</param>
        /// <returns></returns>
        public static bool verifyData(string strVerifyData, string strVerifyType)
        {
            bool Flag = false;
            if (strVerifyData == string.Empty)
                return true;
            try
            {
                switch (strVerifyType.ToLower())
                {
                    case "int":
                        {
                            int i = Convert.ToInt32(strVerifyData);
                            Flag = true;
                            break;
                        }
                    case "date":
                        {
                            DateTime dt = Convert.ToDateTime(strVerifyData);
                            Flag = true;
                            break;
                        }
                    default:
                        { Flag = true; break; }
                }
                return Flag;
            }
            catch
            {
                return Flag;
            }
        }

        /// <summary>
        /// 状态转换
        /// </summary>
        private static string[,] ListArray = new string[,] {
        {"1","处理中"},
        {"2","转单"},
        {"3","报修"},
        {"9","已处理"}
        };

        /// <summary>
        /// 通过描述获取Code
        /// </summary>
        /// <param name="show">描述</param>
        /// <returns></returns>
        public static string getCode(string show)
        {
            string strResult=string.Empty;
            if (show == string.Empty)
                return strResult;
            for (int i = 0; i < ListArray.GetLength(0); i++)
            {
                if (show == ListArray.GetValue(i, 1).ToString())
                {
                    strResult = ListArray.GetValue(i, 0).ToString();
                    break;
                }
            }
            return strResult;
        }

        /// <summary>
        /// 通过code获取描述
        /// </summary>
        /// <param name="code">code</param>
        /// <returns></returns>
        public static string getShow(string code)
        {
            string strResult = string.Empty;
            if (code == string.Empty)
                return strResult;
            for (int i = 0; i < ListArray.GetLength(0); i++)
            {
                if (code == ListArray.GetValue(i, 0).ToString())
                {
                    strResult = ListArray.GetValue(i, 1).ToString();
                    break;
                }
            }
            return strResult;
        }

        public static List<Dictionary<string, string>> getDataList(string strlist)
        {
            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            JsonResult result = JsonConvert.DeserializeObject<JsonResult>(strlist);
            if (result.message == "")
            {
                list = result.data;
            }
            else
                list = null;
            return list;
        }

        /// <summary>
        /// 待转换的Webservice数据
        /// </summary>
        /// <param name="strConvert">待转换的从webservice接收过来的字符串</param>
        /// <returns></returns>
        public static DataSet convertToDataSet(string strConvert)
        {
            DataSet returnDs = new DataSet();
            DataTable dt = new DataTable();
            if (strConvert == string.Empty)
                return null;
            try
            {
                List<Dictionary<string, string>> list = getDataList(strConvert);

                if (list == null)
                    return null;

                //表头
                foreach (Dictionary<string, string> dic in list)
                {
                    foreach (KeyValuePair<string, string> kvp in dic)
                    {
                        if (!dt.Columns.Contains(kvp.Key))
                            dt.Columns.Add(new DataColumn(kvp.Key));
                    }
                    break;
                }

                //数据

                foreach (Dictionary<string, string> dic in list)
                {
                    DataRow dr = dt.NewRow();
                    foreach (KeyValuePair<string, string> kvp in dic)
                    {
                        dr[kvp.Key] = kvp.Value;
                    }
                    dt.Rows.Add(dr);
                }
                returnDs.Tables.Add(dt);
                return returnDs;
            }
            catch
            {
                return null;
            }
            
        }

        /// <summary>
        /// 获取配置文件中关于数据同步的时间间隔
        /// </summary>
        /// <returns></returns>
        public static int GetTime()
        {
            int result = 300000;//默认为5分钟同步一次
            XmlDocument xml = new XmlDocument();
            xml.Load(ConfigPath);
            XmlNode xn = xml.SelectSingleNode("Settings");
            XmlNodeList xnl = xn.ChildNodes;
            foreach (XmlNode xnf in xnl)
            {
                if (xnf.Name == "Time")
                {
                    result = Convert.ToInt32(xnf.InnerText);
                    break;
                }
            }
            return result;
        }
        /// <summary>
        /// 自动创建配置文件
        /// </summary>
        /// <returns></returns>
        public static bool CreateFile()
        {
            bool result=true;
            try
            {
                if (!File.Exists(ConfigPath))
                {
                    string info = "<Settings><Webservice>http://10.134.97.177:8080/pda_web/services/pdaService?wsdl</Webservice><Time>300000</Time><Mac>SDIO86861</Mac></Settings>";
                    XmlDocument xml = new XmlDocument();
                    xml.LoadXml(info);
                    xml.Save(ConfigPath); ;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        
        /// <summary>
        /// 获取配置文件中的信息
        /// </summary>
        /// <returns></returns>
        public static string GetInfo()
        {
            string Webservice=string.Empty;
            string Time=string.Empty;
            string Mac=string.Empty;
            XmlDocument xml = new XmlDocument();
            xml.Load(PublicMethods.ConfigPath);
            XmlNode xn = xml.SelectSingleNode("Settings");
            XmlNodeList xnl = xn.ChildNodes;
            foreach (XmlNode xnf in xnl)
            {
                if (xnf.Name == "Webservice")
                {
                    Webservice = xnf.InnerText;
                }
                else if (xnf.Name == "Time")
                {
                    Time = xnf.InnerText;
                }
                else if (xnf.Name == "Mac")
                {
                    Mac = xnf.InnerText;
                }
            }
            return Webservice + "," + Time + "," + Mac;
        }

        /// <summary>
        /// 数据同步(上传未同步成功的数据)
        /// </summary>
        public static void UploadData()
        {
            try
            {
                string CustomerSql = "select CWOrder_No,status,updatedt,Comment from customerWorkList where status!='1'";
                string InspectionSql = "select ASSIGNMENTID,status,STATUSDATE,remark from inspectionWorkList where status='1'";
                string AmmeterSql = "select MPRId,EM_METER_ID,READDEGREE,READINGTIME from ELECTRICITYMETER where READDEGREE>0";
                string WaterSql = "select MPRId,WM_METER_ID,READDEGREE,READINGTIME from WATERMETER where READDEGREE>0";
                string RepairSql = "select WONUM,status,remark,STATUSDATE from repairWorkList where status!='1'";
                try
                {
                    DataTable Customer = SelectData(CustomerSql).Tables[0];
                    Execute_Customer(Customer);
                }
                catch { }
                try
                {
                    DataTable Inspection = SelectData(InspectionSql).Tables[0];
                    Execute_Inspection(Inspection);
                }
                catch { }
                try
                {
                    DataTable Ammeter = SelectData(AmmeterSql).Tables[0];
                    Execute_Ammeter(Ammeter);
                }
                catch { }
                try
                {
                    DataTable Water = SelectData(WaterSql).Tables[0];
                    Execute_Water(Water);
                }
                catch { }
                try
                {
                    DataTable Repair = SelectData(RepairSql).Tables[0];
                    Execute_Repair(Repair);
                }
                catch { }
            }
            catch (Exception ex)
            {

            }
        }

        //同步客服工单数据
        public static void Execute_Customer(DataTable dt)
        {
            pdaWebReference.PdaService pda = getobject();
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                string customerID = dt.Rows[index]["CWOrder_No"].ToString().Trim();
                string status = dt.Rows[index]["status"].ToString().Trim();
                string time = dt.Rows[index]["updatedt"].ToString().Trim();
                string remark = dt.Rows[index]["Comment"].ToString().Trim();
                try
                {
                    bool Flag = pda.updateWorkOrder(customerID, status, time, remark, Identity.dutyPerson);
                    if (Flag)
                    {
                        string str_Sql = "delete from customerWorkList where CWOrder_No='" + customerID + "'";
                        Flag = PublicMethods.InsertUpdateCreateDropData(str_Sql);
                    }
                }
                catch (Exception ex) { }
            }
        }

        //同步巡检工单数据
        public static void Execute_Inspection(DataTable dt)
        {
            pdaWebReference.PdaService pda = getobject();
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                string inspectionID = dt.Rows[index]["ASSIGNMENTID"].ToString().Trim();
                string status = dt.Rows[index]["status"].ToString().Trim();
                string time = dt.Rows[index]["STATUSDATE"].ToString().Trim();
                string remark = dt.Rows[index]["remark"].ToString().Trim();
                try
                {
                    bool Flag = pda.updateCheckWorkOrder(inspectionID, status, remark, time, Identity.dutyPerson);
                    if (Flag)
                    {
                        string str_Sql = "delete from inspectionWorkList where ASSIGNMENTID='" + inspectionID + "'";
                        Flag = PublicMethods.InsertUpdateCreateDropData(str_Sql);
                    }
                }
                catch (Exception ex) { }
            }
        }

        //同步电表数据
        public static void Execute_Ammeter(DataTable dt)
        {
            pdaWebReference.PdaService pda = getobject();
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                string MPRId = dt.Rows[index]["MPRId"].ToString().Trim();
                string EM_METER_ID = dt.Rows[index]["EM_METER_ID"].ToString().Trim();
                string degree = dt.Rows[index]["READDEGREE"].ToString().Trim();
                string completedt = dt.Rows[index]["READINGTIME"].ToString().Trim();
                try
                {
                    bool blFlag = pda.updateAmmeterReadingWorkOrder(MPRId, degree, completedt, Identity.dutyPerson);
                    if (blFlag)
                    {
                        string delsql = " DELETE FROM ELECTRICITYMETER WHERE EM_METER_ID='" + EM_METER_ID + "'";
                        bool delFlag = PublicMethods.InsertUpdateCreateDropData(delsql);
                    }
                }
                catch (Exception ex) { }
            }
        }

        //同步水表数据
        public static void Execute_Water(DataTable dt)
        {
            pdaWebReference.PdaService pda = getobject();
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                string MPRId = dt.Rows[index]["MPRId"].ToString().Trim();
                string WM_METER_ID = dt.Rows[index]["WM_METER_ID"].ToString().Trim();
                string degree = dt.Rows[index]["READDEGREE"].ToString().Trim();
                string completedt = dt.Rows[index]["READINGTIME"].ToString().Trim();
                try
                {
                    bool blFlag = pda.updateWatermeterReadingWorkOrder(MPRId, degree, completedt, Identity.dutyPerson);
                    if (blFlag)
                    {
                        string delsql = " DELETE FROM WATERMETER WHERE WM_METER_ID='" + WM_METER_ID + "'";
                        //更新成功刪除記錄
                        bool delFlag = PublicMethods.InsertUpdateCreateDropData(delsql);
                    }
                }
                catch (Exception ex) { }
            }
        }

        //同步维修工单数据
        public static void Execute_Repair(DataTable dt)
        {
            pdaWebReference.PdaService pda = getobject();
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                string repairID = dt.Rows[index]["WONUM"].ToString().Trim();
                string status = dt.Rows[index]["status"].ToString().Trim();
                string remark = dt.Rows[index]["remark"].ToString().Trim();
                try
                {
                    bool Flag = pda.updateRepairWorkOrder(repairID, status, remark, Identity.dutyPerson);
                    if (Flag)
                    {
                        string str_Sql = "delete from repairWorkList where WONUM='" + repairID + "'";
                        //更新成功刪除記錄
                        bool delFlag = PublicMethods.InsertUpdateCreateDropData(str_Sql);
                    }
                }
                catch (Exception ex) { }
            }
        }

        public static pdaWebReference.PdaService getobject()
        {
            pdaWebReference.PdaService pda = new PDAMain.pdaWebReference.PdaService();
            string config = GetInfo();
            string[] settings = config.Split(',');
            string Webservice = settings[0];
            pda.Url = Webservice;
            return pda;
        }
    }
}
